[Python]MySQLへのクエリ結果をGoogleスプレッドシートに反映

他のチーム・メンバーとのインターフェースがGoogleスプレッドシートな皆様にとって、いかに楽にGoogleスプレッドシートを更新できるか、というのは頑張りどころです。

今回は Python を使って MySQL へのクエリー結果をGoogleスプレッドシートに反映する方法を紹介します。 カスタマイズすることで、SQL を投げて多種多様なレポートを定期的に作成しているようなエンジニアの生産性向上が期待されます。

環境

• Linux/Mac
• Python: 2.7
• MySQL(ドライバーはMySQL Connectorを利用)

担当システムの制約から Python2.7 を利用しました。Python3 系でも軽微な修正で動作するはずです。

事前準備

必要なライブラリを先にインストールしておきます

Googleスプレッドシート関連

$ pip install -U oauth2client google-api-python-client

MySQL 関連

次の記事を参考に MySQL Connector/Python をインストールしてください。

MySQL Connector/Python 2.1をAmazon Linuxにインストールする

Google Sheets API を使い、スプレッドシートの追加、シートの更新

レポート用の Google スプレッドシートがすでに存在している前提で、新規シートを作成し、そこに、新しいレポートを追加するシナリオを考えます。

Google Sheets API を使い

1. シートの追加
2. シートの更新
3. シートタイトルの変更

で対応します。

また、シートの更新では、クライアントで CSV データを インメモリ に作成し、その内容を反映します。

データベースに SQL を投げたり、S3 のようなオブジェクトストレージからデータを読み込んで、インメモリで CSV データを作成し、そこからスプレッドシートを作成するようなケースで有用です。

プロジェクトの作成

Google Sheets API を利用するために、Google APIキーコンソールに、アプリケーションを登録し、シークレットキーをダウンロードします。

手順は次のブログ記事の 「OAuthを使用する」を参照してください。

Google SpreadSheetにコマンドラインからCSVをアペンドする

スクリプト

MySQL に SQL を投げて、クエリー結果をGoogleスプレッドシートに反映するスクリプトは以下です。

#!/usr/bin/env python
# coding:utf-8

# CSV
import cStringIO
import csv

from pprint import pprint

# MySQL
import mysql.connector

# Google Spreadsheet
import argparse
from apiclient import discovery
import oauth2client
import httplib2

def sql_to_csv():
    """MySQLへのクエリー結果をもとにCSVデータを作成
    """

    # クエリー結果の出力先ファイルオブジェクト
    csvfile = cStringIO.StringIO()
    writer = csv.writer(csvfile, dialect='excel')

    # MySQL に接続
    config = {
        'host': 'HOST',
        'password': 'PASSWORD',
        'user': 'USER_NAME',
        'database': 'DATABASE'
    }
    cnx = mysql.connector.connect(**config)
    cursor = cnx.cursor()

    # SQL を投げる
    cursor.execute("""
  SELECT id, name, enabled
    FROM projects
ORDER BY id
   LIMIT 5
    """)

    # ヘッダー行の出力
    writer.writerow(cursor.column_names)
    for row in cursor:
        writer.writerow(row)

    # MySQL への接続を切る
    cursor.close()
    cnx.close()

    csv_body = csvfile.getvalue()
    csvfile.close()

    return csv_body

def get_sheets_service():
    CLIENT_SECRET_FILE = 'client_secret.json'
    CREDENTIAL_FILE = 'credential.json'
    APPLICATION_NAME = 'XXX'

    store = oauth2client.file.Storage(CREDENTIAL_FILE)
    credentials = store.get()
    if not credentials or credentials.invalid:
        SCOPES = 'https://www.googleapis.com/auth/spreadsheets'
        flow = oauth2client.client.flow_from_clientsecrets(CLIENT_SECRET_FILE, SCOPES)
        flow.user_agent = APPLICATION_NAME
        args = '--auth_host_name localhost --logging_level INFO --noauth_local_webserver'
        flags = argparse.ArgumentParser(parents=[oauth2client.tools.argparser]).parse_args(args.split())
        credentials = oauth2client.tools.run_flow(flow, store, flags)

    http = credentials.authorize(httplib2.Http())
    discoveryUrl = ('https://sheets.googleapis.com/$discovery/rest?' 'version=v4')
    service = discovery.build('sheets', 'v4', http=http, discoveryServiceUrl=discoveryUrl)
    return service

def upload_csv_to_spreadsheet(csv_body):
    """CSVデータをもとにシートを更新
    """
    service = get_sheets_service()
    SPREADSHEET_ID = 'XXX'

    # シートの追加:AddSheetRequest
    requests = {
        'addSheet': {
        }
    }

    body = {
        'requests': requests
    }

    response = service.spreadsheets().batchUpdate(
      spreadsheetId=SPREADSHEET_ID,
      body=body).execute()

    # 作成されたシートIDを取得
    added_sheet_id = response['replies'][0]['addSheet']['properties']['sheetId']

    requests = [
    # CSVデータでシートを更新:PasteDataRequest
    {
        'pasteData': {
            'coordinate':{
              'sheetId': added_sheet_id,
              'rowIndex': 0,
              'columnIndex': 0
            },
            'data':csv_body,
            'type':'PASTE_VALUES',
            'delimiter': ',',

        }
    },

    # シート名の変更:UpdateSheetPropertiesRequest
    {
        'updateSheetProperties': {
            'properties': {
                'title': 'foo-bar-baz',
                'sheetId' : added_sheet_id,
            },
            'fields': 'title'
        }
    }
    ]

    body = {
        'requests': requests
    }

    response = service.spreadsheets().batchUpdate(
      spreadsheetId=SPREADSHEET_ID,
      body=body).execute()

def main():
    csv_body = sql_to_csv()
    upload_csv_to_spreadsheet(csv_body)

if __name__ == '__main__':
    main()

設定の変更

28−33 行目のデータベース接続情報を適宜修正してください。

83 行目のスプレッドシートIDを適宜修正してください。 ブラウザで捜査対象のスプレッドシートで開いたときの URL からスプレッドシートIDを特定できます。

https://docs.google.com/spreadsheets/d/スプレッドシートID/edit#gid=シートID

MySQL への問い合わせでCSVデータを作成

sql_to_csv 関数を実装しています。

インメモリでの書き込みには StringIO ライブラリを利用しています。 Python3系の場合は io ライブラリで代替可能です。

ヘッダー行の出力が不要な場合は、46行目

writer.writerow(cursor.column_names)

をコメントアウトしてください。

Google Sheets API 処理の流れ

Google Sheets API は batchUpdate メソッドで複数の API をまとめて投げられます。

response = service.spreadsheets().batchUpdate(
  spreadsheetId=SPREADSHEET_ID,
  body={'requests' :
    [
      {'API_NAME' : params},
      {'API_NAME' : params},
      ...
    ]}).execute()

今回の処理では

1. シートの追加:AddSheetRequest API
2. シートの更新:PasteDataRequest API
3. シートタイトルの変更:UpdateSheetPropertiesRequest API

の3つの API を投げています。

後ろ2つのAPIでは、シートIDを特定する必要があるため、「シートの追加」APIをまず実行し、シートIDを取得した上で、後ろ2つのAPIをまとめて投げています。

batchUpdate で API コールするときにの API の命名規則にご注意ください。

API リファレンスでの API 名が "FooBarRequest" だった場合、"fooBar" というように

• API 名を小文字で始め
• 最後の Request を除去

する必要があります。

API 名が間違っていると、 "Invalid JSON payload received. Unknown name "update_sheet_properties" at 'requests': Cannot find field." というようなエラーが発生します。

https://stackoverflow.com/a/41133506

シートの追加

AddSheetRequest API を利用します。

{
    'addSheet': {
    }
}

レスポンスをゴニョゴニョして、シートIDを取得します。

# 作成されたシートIDを取得
added_sheet_id = response['replies'][0]['addSheet']['properties']['sheetId']

以降の API でこのシート ID を利用します。

シートの更新

PasteDataRequest API を利用します。

{
    'pasteData': {
        'coordinate':{
          'sheetId': added_sheet_id,
          'rowIndex': 0,
          'columnIndex': 0
        },
        'data':csv_body,
        'type':'PASTE_VALUES',
        'delimiter': ',',

    }
},

• sheetId に、先程追加したシートのIDを指定しています。
• data に、シートに反映するCSVデータを指定しています。
• type が PASTE_VALUES のため、書式を指定せず、値だけがセルに反映されます。フォーマットの詳細は次のドキュメントを参照ください。 https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#PasteType

シートタイトルの変更

UpdateSheetPropertiesRequest API を利用します。

{
    'updateSheetProperties': {
        'properties': {
            'title': 'foo-bar-baz',
            'sheetId' : added_sheet_id,
        },
        'fields': 'title'
    }
}

• sheetId に、先程追加したシートのIDを指定しています。
• title に、変更するシート名を指定しています。

事前準備

スクリプトの実行に関して、最初の実行で、認証が必要です。

$ python add_new_report_to_google_spredsheet.py

Go to the following link in your browser:

    https://accounts.google.com/o/oauth2/auth?scope=https...

Enter verification code: <ここにブラウザに表示されたコードを入力>

具体的な手順は次のブログ記事の 「OAuthを使用する」と「認証を通す」を参照してください。

Google SpreadSheetにコマンドラインからCSVをアペンドする

Google Sheets API 利用に関する処理は get_sheets_service 関数で行っています。

MySQLへのクエリ結果をGoogleスプレッドシートに反映

以上で準備が整いましたので、 スプレッドシートを更新してみましょう。

$ python add_new_report_to_google_spredsheet.py

Googleドライブに移動すると、指定したGoogle スプレッドシートに「foo-bar-baz」という名前のシートが追加されており、シートは MySQL へのクエリ結果で反映されているはずです。

まとめ

MySQL へのクエリー結果をもとに、Googleスプレッドシートをプログラマブルに更新する方法を紹介しました。 この例をもとに、MySQL へのクエリー箇所を S3 からの CSV ファイルの取得など、カスタマイズ可能です。

lazy で impatient なプログラマーのお役に立てれば幸いです。

類似実装

Google SpreadSheetにコマンドラインからCSVをアペンドする

[Python]CSVファイルをGoogleスプレッドシートにアップロードする

参照

• https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request
• OAuth スコープ https://developers.google.com/sheets/api/guides/authorizing